﻿Imports System.Data.SqlClient

Public Class Site
    Inherits System.Web.UI.MasterPage

    Public SQL_CONNECTION_STRING As String = System.Configuration.ConfigurationManager.ConnectionStrings.Item("ApplicationServices").ToString

    Public strMsg As String = ""
    Public strErrorMsg As String = ""

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim sLocalHost As String = ""
        Dim sUserID As String = ""
        Dim sUserName As String = ""
        Dim sUserCOid As String = ""
        Dim sUserCOname As String = ""

        If IsNothing(Session("sUserCOname")) Then
            MyTitle.Text = "MyInventory"
        Else
            MyTitle.Text = "MyInventory :: " & Session("sUserCOname").ToString
        End If

        If IsNothing(Session("HTTP_HOST")) Then

            sLocalHost = Request.ServerVariables("SCRIPT_NAME")
            sLocalHost = sLocalHost.Substring(0, sLocalHost.LastIndexOf("/") + 1)

            Session("HTTP_HOST") = "http://" & Request.ServerVariables("HTTP_HOST") & sLocalHost

        End If

        If IsNothing(Session("iHitCounter")) Then
            Session("iHitCounter") = 0
        End If

        ' We will use the HitCouunter later to avoid DoS attacks. hackers, Spammers and Web Boots
        Session("iHitCounter") = Session("iHitCounter") + 1

        If Context.User.Identity.IsAuthenticated Then

            If IsNothing(Session("sUserName")) Then

                sUserName = Context.User.Identity.Name.Trim.ToLower
                sUserID = FindUserId(sUserName)

                ' EvaluateMembership sets all session variables
                EvaluateMembership(sUserID, sUserName)

                If Session("UIDuserCO") = "" Then

                    Response.Redirect("~/Companies/Companies.aspx")

                End If

            End If

        Else

            Session("sUserName") = Nothing
            Session("UIDuserID") = Nothing
            Session("UIDuserCO") = Nothing
            Session("sUserCOname") = Nothing
            Session("sAccessLevel") = Nothing

        End If

    End Sub

    Private Function FindUserId(ByVal sUserName As String) As String

        Dim sqlConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
        Dim cmd As New SqlCommand

        Dim strSQL As String = _
            "SELECT [UserId] " & _
            "FROM [aspnet_Users] " & _
            "WHERE [LoweredUserName] = '" & sUserName & "'"

        FindUserId = ""

        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.Connection = sqlConnection1

        sqlConnection1.Open()

        Dim rd As SqlDataReader = cmd.ExecuteReader()
        While rd.Read()
            FindUserId = rd.Item(0).ToString.ToUpper
        End While

        sqlConnection1.Close()

        RecordUserVisit(FindUserId)

    End Function

    Private Sub RecordUserVisit(ByVal sUserID As String)
        Dim strSQL As String

        strSQL = _
            "UPDATE [aspnet_Membership] " & _
            "SET [LogginCount] = ([LogginCount] + 1) " & _
            "WHERE [UserId] = '" & sUserID & "'"

        Dim scnnNW As New SqlConnection(SQL_CONNECTION_STRING)
        Dim scmd As New SqlCommand(strSQL, scnnNW)

        ' Normally you can just let the framework bubble errors to the top and
        ' view them in the default ASP .NET error page or handle via settings in
        ' the customErrors element of web.config. In this case, however, it is
        ' nice to anticipate problems and display a message to the user that keeps
        ' them on the same Web page.
        Try
            scnnNW.Open()
            scmd.ExecuteNonQuery()

            ' You must dump the cache of the DataGrid will bind to the existing 
            ' DataView and the product will not appear to have been deleted.

        Catch exp As Exception

            strErrorMsg = "Database error! LogginCounter not saved to the " & _
                "database. Error message: " & exp.Message

            ' WE MUST LOG THIS ERROR IN ERROR_LOG TABLE

        Finally
            scnnNW.Close()
        End Try
    End Sub

    Private Sub EvaluateMembership(ByVal sUserID As String, ByVal sUserName As String)

        Dim sqlConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
        Dim cmd As New SqlCommand
        Dim sUserCOid As String = ""
        Dim sUserCOname As String = ""
        Dim sAccessLevel As String = ""
        Dim bIsVerified As Boolean = False

        Dim strSQL As String = _
            "SELECT [IsEmailVerified], [DefaultCompanyID], [AccessLevel]" & _
            "FROM [aspnet_Membership] " & _
            "WHERE [UserId] = '" & sUserID & "'"

        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.Connection = sqlConnection1

        sqlConnection1.Open()

        Dim rd As SqlDataReader = cmd.ExecuteReader()
        While rd.Read()
            bIsVerified = rd.Item(0)
            sUserCOid = rd.Item(1).ToString.Trim
            sAccessLevel = rd.Item(2).ToString.Trim
        End While
        rd.Close()
        rd = Nothing
        sqlConnection1.Close()

        If sUserCOid <> "" Then
            sUserCOname = GetCompanyName(sUserCOid)
        Else
            sUserCOname = ""
        End If

        Session("UIDuserID") = sUserID
        Session("sUserName") = sUserName
        Session("UIDuserCO") = sUserCOid
        Session("sUserCOname") = sUserCOname
        Session("sAccessLevel") = sAccessLevel



    End Sub

    Private Function GetCompanyName(ByVal sUserCOid As String) As String

        Dim sqlConnection1 As New SqlConnection(SQL_CONNECTION_STRING)
        Dim cmd As New SqlCommand

        Dim strSQL As String = _
            "SELECT [CompanyName] " & _
            "FROM [Companies] " & _
            "WHERE [ID] = '" & sUserCOid.ToLower & "'"

        GetCompanyName = ""

        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.Connection = sqlConnection1

        sqlConnection1.Open()

        Dim rd As SqlDataReader = cmd.ExecuteReader()
        While rd.Read()

            GetCompanyName = rd.Item(0).ToString.Trim.ToUpper

        End While

        sqlConnection1.Close()

    End Function

    Private Sub ActivateActiveCompany()

        Dim strSQL As String

        strSQL = _
                "SELECT ID, CompanyName FROM [Companies] " & _
                "WHERE  [IsActive] = 1 " & _
                "AND  [USERID] = '" & Session("UIDuserID").ToString & "'"

        Dim scnnNW As New SqlConnection(SQL_CONNECTION_STRING)
        Dim scmd As New SqlCommand(strSQL, scnnNW)
        Dim IsActive As Boolean = False

        Session("UIDuserCO") = Nothing
        Session("sUserCOname") = Nothing

        Using connection As New SqlConnection(SQL_CONNECTION_STRING)

            Dim command As New SqlCommand(strSQL, connection)

            connection.Open()

            Dim reader As SqlDataReader = command.ExecuteReader()

            ' Call Read before accessing data.
            While reader.Read()
                Session("UIDuserCO") = String.Format("{0}", reader(0))
                Session("sUserCOname") = String.Format("{0}", reader(1))
                IsActive = True
            End While

            ' Call Close when done reading.
            reader.Close()
        End Using

        If IsActive = False Then
            strErrorMsg = "At least one Company must be active at all times."
        End If

    End Sub

End Class